SQL Tutorial Home

How to Use string_agg()

SQL Tutorial Home

BigQuery provides an aggregated function called string_agg, which can be used to concat several strings, from different rows separated by a specified separator. Due string_agg is an aggregate function you should use the group by clause in the query.

create table Database_test_IB1.teams (team_name string, team_member string);
insert into Database_test_IB1.teams values ('Barcelona','Messi'),('Barcelona','Piquet'),('Barcelona','Xavi');
insert into Database_test_IB1.teams values ('Real Madrid','Ronaldo'),('Real Madrid','Benzema'),('Real Madrid','Ramos');

This is our base table:

select * from Database_test_IB1.teams;

`team_name`	`team_member`
`Real Madrid`	`Ronaldo`
`Real Madrid`	`Benzema`
`Real Madrid`	`Ramos`
`Barcelona`	`Messi`
`Barcelona`	`Piquet`
`Barcelona`	`Xavi`

This function concatenates the values of a given column using a separator, due string_agg is an aggregate function, the group by clause plays an important role to define what rows are grouped.

select team_name, string_agg(team_member,', ') as members
from   Database_test_IB1.teams 
group by team_name;

`team_name`	`members`
`Real Madrid`	`Ronaldo, Benzema, Ramos`
`Barcelona`	`Messi, Piquet, Xavi`

The group by team_name clause creates groups of rows with the same value in team_name, then all the members of the same team are concatenated in the same string.